

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseAbstract {

	Connection conn;

	public DatabaseAbstract() throws SQLException{
		System.out.println("Initialzing Database Connection...");
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		conn =
	      DriverManager.getConnection("jdbc:sqlite:database.db");
	    Statement stat = conn.createStatement();
	    stat.executeUpdate("drop table if exists users;");
	    stat.executeUpdate("create table users (username,password,accountType INTEGER);");
	    
	    stat.executeUpdate("drop table if exists studymaterial;");
	    stat.executeUpdate("create table studymaterial (id INTEGER PRIMARY KEY ASC, name);");
	    
	    stat.executeUpdate("drop table if exists courses;");
	    stat.executeUpdate("create table courses (id INTEGER PRIMARY KEY ASC, name);");
	    
	    stat.executeUpdate("drop table if exists studymaterial_for_course;");
	    stat.executeUpdate("create table courses (cid INTERGER, smid INTEGER);");

	    this.addAdmin("DefaultAdmin", "password");
	    
	    System.out.println("Database Ready to Use!");
		
	}
	
	public void putAccount(String username, String password, int type) throws SQLException{
		PreparedStatement prep = conn.prepareStatement(
	      "insert into users values (?, ?, ?);");

	    prep.setString(1, username);
	    prep.setString(2, password);
	    prep.setInt(3, type);
	    prep.addBatch();
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	}
	
	public void addAdmin(String username, String password) throws SQLException{
		this.putAccount(username,password,1);
	}
	
	public void addTeacher(String username, String password) throws SQLException{
		this.putAccount(username,password,2);
	}
	
	public void addStudent(String username, String password) throws SQLException{
		this.putAccount(username,password,3);
	}
	
	public int getUser(String username, String password) throws SQLException{
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("select * from users where username='"+username+"' and password='"+password+"';");
		while(rs.next()){
			return rs.getInt("accountType");
		}
		return 0;
	}
	
	public void editAccount(int accountType,String name) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("update users set accountType='"+accountType+"' where username='"+name+"';");

	}
	
	public void removeAccount(String name) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("delete from users where username='"+name+"';");
	}
	
	public void listUsers() throws SQLException{
		System.out.println("List Users:");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("select * from users;");
	    while (rs.next()) {
	      System.out.println("username = " + rs.getString("username"));
	      System.out.println("password = " + rs.getString("password"));
	      System.out.println("accountType = " + rs.getInt("accountType"));
	    }
	    rs.close();
	}
	
	public void createCourse(String name) throws SQLException{
		PreparedStatement prep = conn.prepareStatement(
	      "insert into course values (rowid, ?);");

	    prep.setString(1,name);

	    prep.addBatch();
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	}
	public void updateCourse(int id , String name) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("update course set name='"+name+"' where id='"+id+"';");
	}
	
	public void removeCourse(int id) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("delete from course where id='"+id+"';");
	}
	
	public void createStudyMaterial(String name) throws SQLException{
		PreparedStatement prep = conn.prepareStatement(
	      "insert into studymaterial values (rowid, ?);");

	    prep.setString(1,name);

	    prep.addBatch();
	    
	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);
	}
	public void updateStudyMaterial(int id , String name) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("update studymaterial set name='"+name+"' where id='"+id+"';");
	}
	
	public void removeStudyMaterial(int id) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("delete from studymaterial where id='"+id+"';");
	}
	
	public void addStudyMaterialForCourse(int cid, int smid) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("insert into studymaterial_for_course values ('"+cid+"','" + smid + "');");
	}
	public void removeStudyMaterialFromCourse(int cid,int smid) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("delete from studymaterial_for_course where cid='"+cid+"' and smid ='" +smid +"' ;");
	}
	
	public void removeStudyMaterialFromAllCourse(int smid) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("delete from studymaterial where id='"+smid+"';");
	}
	
	public void removeStudyMaterialFromThisCourse(int cid) throws SQLException{
		Statement stat = conn.createStatement();
		stat.execute("delete from studymaterial_for_course where cid='"+cid+"';");
	}
}
